Day8 groupby, having

今天用同一張表繼續介紹 having，groupby

今天用到的 table

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    salesperson VARCHAR(255),
    region VARCHAR(255),
    amount NUMERIC
);

INSERT INTO sales (salesperson, region, amount) VALUES
('Alice', 'North', 500),
('Bob', 'North', 600),
('Alice', 'South', 700),
('Charlie', 'East', 400),
('Alice', 'North', 200),
('Bob', 'South', 300);

簡單來說，groupy by 就是將同樣的值集合在一起

我們 select * from sales 如下

| ID  | salesperson | Region | Amount |
| --- | ------------| ------ | ------ |
| 1   | Alice       | North  | 500    |
| 2   | Bob         | North  | 600    |
| 3   | Alice       | South  | 700    |
| 4   | Charlie     | East   | 400    |
| 5   | Alice       | North  | 200    |
| 6   | Bob         | South  | 300    |

簡單 group by 分組

select salesperson
from sales
group by salesperson

就會返回以下結果

salesperson
Alice
Bob
Charlie

不同 record(row) 被分組在一起了

這時候可以配合 Amount 做很簡單的加總統計

select salesperson, sum(amount) as total
from sales
group by salesperson

結果

| salesperson    | total |
| -------------- | ----- |
| Alice          | 1400  |
| Bob            | 900   |
| Charlie        | 400   |

加上 having

select salesperson, sum(amount) as total
from sales
group by salesperson
having sum(amount) > 1000

結果

| salesperson    | total |
| -------------- | ----- |
| Alice          | 1400  |

既然 where 跟 having 都是一個 filter 概念 差在哪？

where 是對欄位下條件，having 是對計算完的結果下條件，所以 having 常在聚合計算中出現